package com.huaxia.dao.attend;

import com.huaxia.pojo.attend.BranchAttend;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/03 14:25
 */

public interface BranchAttendMapper {

    @Select("select a.branchsname,a.branchDaySignNum,b.branchYesterdaySignNum,c.branchMonAvg,round(decode(b.branchYesterdaySignNum,0,-9999,(a.branchDaySignNum-b.branchYesterdaySignNum)*100/b.branchYesterdaySignNum))attendGrowth,d.branchNewMonSign from\n" +
            "(select sb.branchsname,NVL(a.num,0)branchDaySignNum from \n" +
            "(select a.branchcode,count(a.branchcode)num from\n" +
            "(select substr(t.managecom,0,6)branchcode,t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd') \n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)a group by a.branchcode)a right join SD_BRANCH sb on a.branchcode=sb.branchcode\n" +
            ")a,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchYesterdaySignNum from \n" +
            "(select a.branchcode,count(a.branchcode)num from\n" +
            "(select substr(t.managecom,0,6)branchcode,t.staffcode from LTATTENDANCE t where \n" +
            "(case when to_char(sysdate,'d')=2 then to_char(sysdate-2,'yyyy/mm/dd') else to_char(sysdate-1,'yyyy/mm/dd') end)=to_char(t.attendate,'yyyy/mm/dd')\n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)a group by a.branchcode)a right join SD_BRANCH sb on a.branchcode=sb.branchcode\n" +
            ")b,\n" +
            "(select a.branchsname,round(decode(b.num,0,0,a.branchMonSignNum/b.num)) branchMonAvg from\n" +
            "(select sb.branchsname,NVL(a.num,0)branchMonSignNum from \n" +
            "(select substr(t.managecom,0,6)branchcode ,count(t.managecom)num from  LTATTENDANCE t where  t.staffcode is not null and t.attendancetype='morning' \n" +
            "and to_char(sysdate,'yyyy/mm')=to_char(t.attendate,'yyyy/mm') and to_char(t.attendate,'d')!=1 group by  substr(t.managecom,0,6))a right join SD_BRANCH sb on a.branchcode=sb.branchcode)a,\n" +
            "(select count(1)num from (\n" +
            "  select to_char(TO_DATE(TO_CHAR(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') - level, 'd') DOW from dual\n" +
            "connect by level <= trunc(to_date(TO_CHAR(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') - TRUNC(SYSDATE,'MM'))+1\n" +
            ") where DOW!=7)b\n" +
            ")c,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchNewMonSign from\n" +
            "(select a.branchcode,count(a.branchcode)num  from\n" +
            "(select  b.branchcode,b.agentcode from\n" +
            "(select t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd') \n" +
            "and t.staffcode is not null group by t.staffcode)a,\n" +
            "(select  t.branchcode,t.agentcode from D_AGENT_PROVINCE_YX2 t where \n" +
            "to_char(t.employdate,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职' group by t.agentcode, t.branchcode)b\n" +
            "where a.staffcode=b.agentcode)a group by a.branchcode)a right join SD_BRANCH sb on a.branchcode=sb.branchcode)d \n" +
            "where a.branchsname=b.branchsname and b.branchsname=c.branchsname and c.branchsname=d.branchsname order by a.branchDaySignNum Desc")
    List<BranchAttend> getBranchAttend();
}
